<?php

/**
 * Mysql类库
 *
 */
class Helper_MysqlDb {

    protected $db;

    public function __construct($config) {
        $this->db = mysql_connect("{$config['host']}:{$config['port']}", $config['user'], $config['passwd'], true, MYSQL_CLIENT_INTERACTIVE);
        //print_r($this->db);
        if ($this->db) {
            mysql_query("SET NAMES 'utf8'");
            if (isset($config['name'])) {
                return mysql_select_db($config['name'], $this->db);
            }

            return $this->db;
        } else {
            return false;
        }
    }

    public function selectDb($name) {
        mysql_select_db($name, $this->db);
    }

    public function query($sql) {

        $ret = mysql_query($sql, $this->db);
        return $ret;
    }

    /**
     * 获取一条记录
     *
     * @param string $table
     * @param array $where
     * @return array
     */
    public function get($table, $where, $columns = null) {
        if ($columns === null) {

            $columnList = '*';
        } elseif (!is_array($columns)) {
            throw new AppException(303);
        } else {
            $columnList = self::_makeColumn($columns);
        }

        $sql = "SELECT {$columnList} FROM `{$table}` WHERE " . self::_makeWhere($where) . " LIMIT 1";
        //echo $sql;
        return $this->find($sql);
    }

    /**
     * 获取多条记录
     * @param string $table
     * @param array | string $where
     * @param array $order
     * @param int $limit
     * @param int $start
     * @return array
     */
    public function getList($table, $where, $order = array(), $start = 0, $limit = 0) {
        $sql = "SELECT * FROM `{$table}` WHERE " . self::_makeWhere($where);

        if ($order) {
            $sql .= " ORDER BY " . self::_makeOrderValue($order);
        }

        if ($limit > 0) {
            $sql .= " LIMIT {$start} , {$limit}";
        }
       //echo $sql;

        return $this->findAll($sql);
    }

    /**
     * 查询多条记录（针对同一个字段）
     * 
     */
    public function getMultiList($table, $column, $where, $start = 0, $limit = 0) {

        $sql = "SELECT * FROM `{$table}` WHERE " . self::_columnWhere($column, $where);

        if ($limit > 0) {
            $sql .= " LIMIT {$start} , {$limit}";
        }
       //echo $sql;
        return $this->findAll($sql);
    }
    /**
     * 查询返回多条记录
     * 
     */

    public function getListTo($field, $table, $where) {
        $sql = "SELECT {$field} FROM {$table} WHERE {$where}";
        //echo $sql;
        return $this->findAll($sql);
    }

    /**
     * 删除
     * @param string $table
     * @param array $where
     * @param int $limit
     * @return int
     */
    public function delete($table, $where, $limit = 0) {
        $sql = "DELETE FROM `{$table}` WHERE " . self::_makeWhere($where);
        if ($limit > 0) {
            $sql .= " LIMIT {$limit}";
        }

        $this->query($sql);
        return $this->affectedRows();
    }

    /**
     * 添加一条记录
     * @param string $table
     * @param array $value
     * @return boolean
     */
    public function add($table, $value) {
        $sql = "INSERT INTO `{$table}` " . self::_makeInsertValue($value);
        //echo $sql;
        $this->query($sql);
        return $this->affectedRows();
    }

    /**
     * 更新一条记录
     * @param string $table
     * @param array $where
     * @param array $value
     * @return int
     */
    public function update( $table, $where, $value, $limit = 0) {
        $sql = "UPDATE `{$table}` SET " . self::_makeUpdateValue($value) . " WHERE " . self::_makeWhere($where);
        if ($limit > 0) {
            $sql .= " LIMIT {$limit}";
        }
      //  echo $sql;

        $this->query($sql);
        return $this->affectedRows();
    }

    /**
     * 获取多条记录
     *
     * @param string $sql
     * @return array
     */
    public function findAll($sql) {
        $result = $this->query($sql);
        //print_r($result);
        //return empty($result)?NULL:$this->res2Assoc($result);
        return $this->res2Assoc($result);
    }

    /**
     * 获取单条记录
     *
     * @param string $query
     * @return array
     */
    public function find($query) {

        $result = $this->query($query);
        return $this->fetchAssoc($result);
    }

    public function fetchAssoc($result) {
        return mysql_fetch_assoc($result);
    }

    /**
     * 获取影响行数
     *
     * @return int
     */
    public function affectedRows() {
        return mysql_affected_rows($this->db);
    }

    /**
     * 获取Insert ID
     *
     * @return int
     */
    public function insertId() {
        return mysql_insert_id($this->db);
    }

    /**
     * 获取记录数量
     *
     * @param array $where
     * @return int
     */
    public function getCount($tables, $where = array()) {
        $sql = "SELECT COUNT(*) AS `count` FROM `{$tables}`";
        if ($where) {
            $sql .= " WHERE " . self::_makeWhere($where);
        }
        $data = $this->find($sql);
        return $data['count'];
    }

    public function & res2Assoc(& $res) {
        $rows = array();
        while ($row = $this->fetchAssoc($res)) {
            $rows[] = $row;
        }
        return $rows;
    }

    public function startTransaction() {
        mysql_query("SET AUTOCOMMIT=0", $this->db);
        mysql_query("START TRANSACTION", $this->db);
    }

    public function commitTransaction() {
        mysql_query("COMMIT");
        mysql_query("SET AUTOCOMMIT=1", $this->db);
    }

    public function rollbackTransaction() {
        mysql_query("ROLLBACK", $this->db);
    }

    /**
     * 获取错误号码
     *
     * @return integer
     */
    public function getErrorNumber() {
        return mysql_errno($this->db);
    }

    /**
     * 获取错误消息
     *
     * @return string
     */
    public function getErrorMessage() {
        return mysql_error($this->db);
    }

    /**
     * 拼接列语句
     *
     * @param array $columns
     * @return string
     */
    public static function _makeColumn($columns) {
        $columList = array();
        foreach ($columns as $column) {
            $columList[] = "`{$column}`";
        }

        return implode(" , ", $columList);
    }

    /**
     * 拼接where语句
     * @param type $column
     * @param type $where 
     * 
     */
    public function _columnWhere($column, $where) {
        foreach ($where as $value) {
            $columnList[] = "`{$column}` = {$value}";
        }
        // print_R( $columnList );
        // print_r($columnList);
        return implode('  ||  ', $columnList);
    }

    /**
     * 拼接条件语句
     *
     * @param array | string $where
     * @return string
     */
    public static function _makeWhere($where) {
        if (is_array($where)) {
            $whereList = array();
            foreach ($where as $key => $value) {
                if (is_numeric($key)) {
                    $whereList[] = $value;
                } else {
                    $whereList[] = "`{$key}` = '{$value}'";
                }
            }

            $where = implode(" && ", $whereList);
        }
        return $where;
    }

    /**
     * 拼接更新SQL
     *
     * @param array $data
     * @return string
     */
    public static function _makeUpdateValue($data) {
        if (is_string($data)) {
            return $data;
        }

        $dataList = array();
        foreach ($data as $key => $value) {
            $dataList[] = "`{$key}` = '{$value}'";
        }

        return implode(" , ", $dataList);
    }

    /**
     * 拼接更新SQL
     *
     * @param array $data
     * @return string
     */
    public static function _makeInsertValue($value) {
        $keyString = implode("` , `", array_keys($value));
        $valueString = implode("' , '", $value);
        return "( `{$keyString}` ) VALUES ( '{$valueString}' )";
    }

    /**
     * 拼接排序SQL
     * @param array $order
     * @return string
     */
    public static function _makeOrderValue($order) {
        $orderList = array();
        foreach ($order as $key => $value) {
            $orderList[] = "`{$key}` {$value}";
        }

        return implode(" , ", $orderList);
    }

}